﻿using System;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Collections;
using System.Drawing;
using System.Threading;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelToChart
{
    public partial class Form1 : Form
    {
        FormProgressBar fpb;
        Form_SheetSelect fss;

        int rowIndex;
        int columnIndexAtMouse;
        int selectedTabIndex;
        int tabpageMouseHover = 0;

        public string filePath { set; get; }
        FileStream stream;
        public IWorkbook workbook;
        public bool isCompatible;
        public List<string> activateSheetNames = new List<string>();
        public List<SheetDataTable> sheetInfo = new List<SheetDataTable>();

        DataSet dataOfAllSheets = new DataSet();

        public Form1()
        {
            InitializeComponent();

            textBox_filePathLastTime.Text = Properties.Settings.Default.filePathLastTime;
        }

        public static void PrintTable(DataTable table)
        {
            // print head
            PrintLine(12 * table.Columns.Count);
            foreach (DataColumn col in table.Columns)
            {
                Console.Write(string.Format("{0,12}", col.Caption));
            }
            Console.Write("\n");
            PrintLine(12 * table.Columns.Count);

            // print rows
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    Console.Write(string.Format("{0,12}", table.Rows[i][j].ToString()));
                }
                Console.Write("\n");
            }
            PrintLine(12 * table.Columns.Count, "-");
        }

        /// <summary>
        /// Print a line with specific char on to the console
        /// </summary>
        /// <param name="length">count of the char to be printed</param>
        /// <param name="lineChar">the char to be printed, default is "="</param>
        private static void PrintLine(int length, string lineChar = "=")
        {
            string line = string.Empty;
            for (int i = 0; i < length; i++)
            {
                line += lineChar;
            }
            Console.WriteLine(line);
        }

        private DataTable CreateDataTableFromContent()
        {
            DataTable dts = dataGridView1.DataSource as DataTable;
            DataTable dtd = new DataTable();

            for (int i = dataGridView1.RowCount - 1; i >= 0; i--)
            {
                if (dataGridView1.Rows[i].DefaultCellStyle.ForeColor == Color.Red)
                {
                    dts.Rows[i].Delete();
                }
            }

            dts.AcceptChanges();

            for (int i = dataGridView1.ColumnCount - 1; i >= 0; i--)
            {
                if (dataGridView1.Columns[i].DefaultCellStyle.ForeColor == Color.Red)
                {
                    dts.Columns.RemoveAt(i);
                }
            }

            PrintTable(dts);

            return dts;
        }

        private string selectExcelFile()
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            if (string.IsNullOrEmpty(Properties.Settings.Default.folderLastTime))
            {
                file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            }
            else
            {
                file.InitialDirectory = Properties.Settings.Default.folderLastTime;
            }

            file.Multiselect = false;

            if (file.ShowDialog() == DialogResult.Cancel)
                return "";

            return file.FileName; ;
        }

        private void tabControl1_MouseClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                contextMenuStrip_tabcontrol.Show(MousePosition);

                foreach (TabPage tp in tabControl1.TabPages)
                {
                    if (tp.Bounds.Contains(e.X, e.Y))
                        MessageBox.Show(tp.Text);
                }
            }
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tabControl1.SelectedIndex != -1)
            {
                selectedTabIndex = tabControl1.SelectedIndex;
                Console.WriteLine(string.Format("selectpage {0} of {1}: ", selectedTabIndex, tabControl1.TabCount));
                dataGridView1.DataSource = null;
                dataGridView1.DataSource = dataOfAllSheets.Tables[selectedTabIndex];
            }
        }

        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            Console.WriteLine(string.Format("dataGridView1_CellMouseUp at r:{0} c:{1}", e.RowIndex, e.ColumnIndex));
            if (e.Button == MouseButtons.Right)
            {
                // click at column header(-1 row)
                if (e.RowIndex == -1)
                {
                    columnIndexAtMouse = e.ColumnIndex;
                    contextMenuStrip_ColumnOps.Show(Cursor.Position);
                    return;
                }

                // click at first column
                if (e.ColumnIndex == -1)
                {
                    contextMenuStrip_RowOps.Show(Cursor.Position);
                    return;
                }

                // normal cell clicked
                return;
            }
        }

        private void dataGridView1_SortCompare(object sender, DataGridViewSortCompareEventArgs e)
        {
            dataGridView1.Sort(new NaturalSortComparer());
            MessageBox.Show("SortCompare " + e.ToString());
        }

        private void dataGridView1_Sorted(object sender, EventArgs e)
        {
            MessageBox.Show("TODO dataGridView1_Sorted");
        }

        private void buttonLoadFile_Click(object sender, EventArgs e)
        {
            string path = textBox_filePathLastTime.Text;
            if (!File.Exists(path))
            {
                MessageBox.Show("文件不存在,请重新选择 " + path);
                return;
            }

            string extension = Path.GetExtension(path);
            if (!path.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) && !path.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                MessageBox.Show("文件扩展名错误, 请重新选择 " + extension);
                return;
            }

            // 记录本次选择的文件夹
            string folder = Path.GetDirectoryName(path);
            Properties.Settings.Default.folderLastTime = folder;
            Properties.Settings.Default.Save();

            Properties.Settings.Default.ignoreEmptyRow = false;
            Properties.Settings.Default.Save();

            #region make workbook from file
            try
            {
                stream = System.IO.File.OpenRead(filePath);
                isCompatible = filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);

                if (isCompatible)
                {
                    workbook = new HSSFWorkbook(stream);
                }
                else
                {
                    workbook = new XSSFWorkbook(stream);
                }

                Console.WriteLine(workbook.NumberOfSheets);

                stream.Close();
            }
            catch
            {
                MessageBox.Show("请关闭文件,再导入！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                if (stream != null)
                {
                    stream.Close();
                }

            }
            #endregion

            #region get all sheet names
            activateSheetNames = ExcelHelper.GetSheetNames(workbook);
            #endregion

            #region make dataset from activated sheet name
            fss = new Form_SheetSelect(activateSheetNames);
            fss.Owner = this;

            DialogResult dr = fss.ShowDialog();
          
            if (dr == DialogResult.OK)
            {
                Console.WriteLine("sheet select ok");

                if (activateSheetNames.Count == 0)
                {
                    Console.WriteLine("no sheet is selected");
                    return;
                }

                activateSheetNames = fss.selectedSheetNames;

                dataOfAllSheets = new DataSet();
                foreach (string sn in activateSheetNames)
                {
                    //Console.WriteLine(sn);
                    ISheet sheet = workbook.GetSheet(sn);
                    DataTable dt = ExcelHelper.GetDataTableFromSheet(sheet, 2);
                    dataOfAllSheets.Tables.Add(dt);
                }
            }
            else
            {
                Console.WriteLine("sheet select cancel");
                return;
            }

            

            Console.WriteLine("total datatable count:" + dataOfAllSheets.Tables.Count.ToString());

            #endregion

            #region show tabcontrol, tabpages

            tabControl1.TabPages.Clear();

            foreach (string sn in activateSheetNames)
            {
                if (sn != "")
                {
                    TabPage page = new TabPage();
                    page.Name = sn;
                    page.Text = sn;
                    tabControl1.TabPages.Add(page);
                }
            }
            selectedTabIndex = 0;

            #endregion

            #region show default datagridview

            dataGridView1.DataSource = null; //每次打开清空内容
            dataGridView1.Columns.Clear();
            dataGridView1.DataSource = dataOfAllSheets.Tables[selectedTabIndex];

            foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
            {
                dgvc.SortMode = DataGridViewColumnSortMode.NotSortable;
            }

            #endregion

            textBox_filePathLastTime.Text = path;
            Properties.Settings.Default.filePathLastTime = path;
            Properties.Settings.Default.Save();
        }

        private void buttonSelectFile_Click(object sender, EventArgs e)
        {
            string path = selectExcelFile();
            textBox_filePathLastTime.Text = path;
        }

        private void button_keep_rows_by_regex_Click(object sender, EventArgs e)
        {
            string pattern = textBox_regex_filter.Text;

            List<string> strs = new List<string>();
            for (int row = 1; row < dataGridView1.RowCount - 1; row++)
            {
                string v = dataGridView1[0, row].Value.ToString();
                strs.Add(v);

                Match m = Regex.Match(v, pattern);
                if (m.Success)
                {
                    dataGridView1.Rows[row].Selected = true;
                }
            }
        }

        private void button_remove_rows_by_regex_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (row.Selected == true)
                    {
                        row.Selected = false;
                    }
                    else
                    {
                        row.Selected = true;
                    }
                }

                dataGridView1.Rows[dataGridView1.NewRowIndex].Selected = false;

            }
        }

        private void button_select_row_all_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                row.Selected = true;
            }
        }

        private void button_selete_row_tag_as_deleted_Click(object sender, EventArgs e)
        {
            if (dataGridView1.Rows.Count > 0)
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (row.DefaultCellStyle.ForeColor == Color.Red)
                    {
                        row.Selected = true;
                    }
                    else
                    {
                        row.Selected = false;
                    }
                }
            }
        }

        private void button_disselect_row_all_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                row.Selected = false;
            }
        }

        private void button_delete_selected_rows_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow row in dataGridView1.SelectedRows)
            {
                //dataGridView1.Rows.Remove(row);
                row.DefaultCellStyle.ForeColor = Color.Red;
                row.Selected = false;
            }
        }

        private void button_show_line_chart_Click(object sender, EventArgs e)
        {
            Form_Chart fc = new Form_Chart(CreateDataTableFromContent());
            fc.Show();
        }

        private void button_show_Histogram_Click(object sender, EventArgs e)
        {
            Form_Chart fc = new Form_Chart(CreateDataTableFromContent());
            fc.Show();
        }

        private void ToolStripMenuItem_tablcontrol_remove_page_Click(object sender, EventArgs e)
        {
            tabControl1.TabPages.RemoveAt(tabpageMouseHover);
        }

        private void ToolStripMenuItem_delCurrentColumn_Click(object sender, EventArgs e)
        {
            //if(!this.dataGridView1.Columns[this.columnIndex].is)
            //this.dataGridView1.Columns.RemoveAt(this.columnIndex);
            //dataGridView1.Columns[columnIndex].DefaultCellStyle.ForeColor = Color.Red;
            if (dataGridView1.Columns[columnIndexAtMouse].DefaultCellStyle.ForeColor == Color.Red)
            {
                dataGridView1.Columns[columnIndexAtMouse].DefaultCellStyle.ForeColor = Color.Empty;
            }
            else
            {
                dataGridView1.Columns[columnIndexAtMouse].DefaultCellStyle.ForeColor = Color.Red;
            }
        }

        private void ToolStripMenuItem_delAllColumnsRightside_Click(object sender, EventArgs e)
        {
            for (int clm = this.columnIndexAtMouse + 1; clm < dataGridView1.Columns.Count; clm++)
            {
                //this.dataGridView1.Columns.RemoveAt(this.columnIndex);
                //dataGridView1.Columns[clm].Visible = false;
                //dataGridView1.Columns[clm].DefaultCellStyle.ForeColor = Color.Red;
                if (dataGridView1.Columns[clm].DefaultCellStyle.ForeColor == Color.Red)
                {
                    dataGridView1.Columns[clm].DefaultCellStyle.ForeColor = Color.Empty;
                }
                else
                {
                    dataGridView1.Columns[clm].DefaultCellStyle.ForeColor = Color.Red;
                }

            }
        }

        private void ToolStripMenuItem_modify_column_name_Click(object sender, EventArgs e)
        {
            // TODO
            dataGridView1.Columns[columnIndexAtMouse].HeaderText = "new name";
        }

        private void ToolStripMenuItem_opentestinteropexcel_Click(object sender, EventArgs e)
        {

        }

        private void ToolStripMenuItem_SetAsHeader_Click(object sender, EventArgs e)
        {
            DataGridViewRow rowSelected = dataGridView1.SelectedRows[0];
            int selectedRowIndex = rowSelected.Index;

            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                dataGridView1.Columns[i].HeaderCell.Value = rowSelected.Cells[i].Value;
            }

            for (int i = 0; i <= selectedRowIndex; i++)
            {
                (dataGridView1.DataSource as DataTable).Rows.RemoveAt(0);
            }
        }

        private void ToolStripMenuItem_deleteCurrentRow_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow dgvr in dataGridView1.SelectedRows)
            {
                if (!dgvr.IsNewRow)
                {
                    if (dgvr.DefaultCellStyle.ForeColor == Color.Empty)
                    {
                        dgvr.DefaultCellStyle.ForeColor = Color.Red;
                    }
                    else
                    {
                        dgvr.DefaultCellStyle.ForeColor = Color.Empty;
                    }
                }
            }
        }

        private void ToolStripMenuItem_deleteAllRowsBelow_Click(object sender, EventArgs e)
        {
            for (int rowId = rowIndex + 1; rowId < dataGridView1.Rows.Count - 1; rowId++)
            {
                dataGridView1.Rows[rowId].DefaultCellStyle.ForeColor = Color.Red;
            }
        }

        private void ToolStripMenuItem_displayRowColor_Click(object sender, EventArgs e)
        {
            Color c = dataGridView1.Rows[rowIndex].DefaultCellStyle.ForeColor;
            MessageBox.Show("color: " + c.ToString());
        }

        private void tabControl1_MouseMove(object sender, MouseEventArgs e)
        {
            for (var i = 0; i < this.tabControl1.TabPages.Count; i++)
            {
                var tabRect = this.tabControl1.GetTabRect(i);
                if (tabRect.Contains(e.Location))
                {
                    //Console.WriteLine(tabControl1.TabPages[i].Text);
                    tabpageMouseHover = i;
                }
            }
        }

        private void button_filter_preview_enable_Click(object sender, EventArgs e)
        {
            CurrencyManager currencyManager1 = (CurrencyManager)BindingContext[dataGridView1.DataSource];
            currencyManager1.SuspendBinding();

            foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
            {
                if (dgvc.DefaultCellStyle.ForeColor == Color.Red)
                {
                    dgvc.Visible = false;
                }
            }

            for (int rn = dataGridView1.Rows.Count - 1; rn >= 0; rn--)
            {
                if (dataGridView1.Rows[rn].DefaultCellStyle.ForeColor == Color.Red)
                {
                    dataGridView1.Rows[rn].Visible = false;
                }
            }

            currencyManager1.ResumeBinding();
        }

        private void button_filter_preview_disable_Click(object sender, EventArgs e)
        {
            CurrencyManager currencyManager1 = (CurrencyManager)BindingContext[dataGridView1.DataSource];
            currencyManager1.SuspendBinding();

            foreach (DataGridViewColumn dgvc in dataGridView1.Columns)
            {
                dgvc.Visible = true;
            }

            for (int rn = dataGridView1.Rows.Count - 1; rn >= 0; rn--)
            {
                dataGridView1.Rows[rn].Visible = true;
            }

            currencyManager1.ResumeBinding();
        }

        private void button_getContentAbove_Click(object sender, EventArgs e)
        {
            int cellsInColumn = dataGridView1.Rows.Count;

            for (int i = 0; i < cellsInColumn - 1; i++)
            {
                DataGridViewRow dgvr = dataGridView1.Rows[i];
                if (dgvr.DefaultCellStyle.ForeColor != Color.Red)
                {
                    if (i - 1 < 0)
                    {
                        continue;
                    }
                    string s1 = dgvr.Cells[0].Value.ToString();
                    string s2 = dataGridView1.Rows[i - 1].Cells[0].Value.ToString();
                    string s = string.Format("{0}-{1}", s1, s2);


                    //dgvr.Cells[0].Value = dgvr.Cells[0].Value.ToString() + dataGridView1.Rows[i].Cells[0].Value.ToString();
                    dgvr.Cells[0].Value = s;
                }
            }
        }

        private void button_applyRowRuleToAllSheet_Click(object sender, EventArgs e)
        {

        }

        private void button_applyColumnRuleToAllSheet_Click(object sender, EventArgs e)
        {

        }

        private void formProgressToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormProgressBar fpb = new FormProgressBar();
            fpb.Show();
        }

        private void backgroundWorker1_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
        {

        }

        private void backgroundWorker1_ProgressChanged(object sender, System.ComponentModel.ProgressChangedEventArgs e)
        {

        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {

        }

        private void textBox_filePathLastTime_TextChanged(object sender, EventArgs e)
        {
            filePath = textBox_filePathLastTime.Text;
        }

        private void ToolStripMenuItem_CloseMainForm_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void ToolStripMenuItem_OpenFile_Click(object sender, EventArgs e)
        {
            filePath = selectExcelFile();
            toolStripStatusLabel2.Text = filePath;
        }

        private void button_FormatFirstColumn_Click(object sender, EventArgs e)
        {
            List<string> originTextList = new List<string>();
            List<string> formatTextList = new List<string>();

            for (int rn = 0; rn < dataGridView1.Rows.Count - 1; rn++)
            {
                DataGridViewCell dgvc = dataGridView1.Rows[rn].Cells[0];

                if (dgvc.Visible == false || dataGridView1.Rows[rn].DefaultCellStyle.ForeColor == Color.Red)
                {
                    continue;
                }

                originTextList.Add(dgvc.Value.ToString());
                formatTextList.Add(dgvc.Value.ToString());
            }

            Form_DateFormat fdp = new Form_DateFormat(originTextList, formatTextList);
            fdp.ShowDialog();
        }

        private void formDatePreviewToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form_DateFormat fdp = new Form_DateFormat();
            fdp.ShowDialog();
        }

        private void testPartialClassToolStripMenuItem_Click(object sender, EventArgs e)
        {
            myPartialFunction();
        }

        private void ToolStripMenuItem_setDateColumn_Click(object sender, EventArgs e)
        {
            Console.WriteLine("ToolStripMenuItem_setDateColumn_Click");
            dataGridView1.Columns[columnIndexAtMouse].DefaultCellStyle.BackColor = Color.FromArgb(200, 255, 255);
        }

        private void ToolStripMenuItem_setNumberColumn_Click(object sender, EventArgs e)
        {
            Console.WriteLine("ToolStripMenuItem_setNumberColumn_Click");
            dataGridView1.Columns[columnIndexAtMouse].DefaultCellStyle.BackColor = Color.FromArgb(255, 200, 255);
        }

        private void testFormCombinedSheetToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form_CombinedSheet fcs = new Form_CombinedSheet();


            fcs.ShowDialog();
        }
    }
}
